﻿using MSL.DataBase.Core;
using MSL.DataBase.Lib;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.IO;

namespace DataBase.Core
{
    /// <summary>
    /// 文本数据库操作
    /// </summary>
    internal class TxtHelper : DbBase
    {
        private string path = "";//文本数据库路径 
        private string tableName = "";
        private Hashtable tableHT = new Hashtable();//数据表
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="conn">链接字符串</param>
        public TxtHelper(string conn)
            : base(DataBaseType.Txt, conn, DBConfig.Txt)
        {

        }
        public TxtHelper()
            : base(DataBaseType.Txt, DBConfig.ConnString, DBConfig.Txt)
        {

        }
        #region 执行

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="commandTexts">sql语句</param>
        /// <returns></returns>
        public new int ExecSql(string[] commandTexts)
        {
            int affectedRows = 0;


            return affectedRows;
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <returns></returns>
        public new int ExecSql(string commandText, bool isProc)
        {
            return 0;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否为存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new int ExecSql(string commandText, bool isProc, DbParameter[] parameters)
        {
            return 0;
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new int ExecSql(string commandText, bool isProc, Hashtable parameters)
        {
            int affectedRows = 1;

            return affectedRows;
        }

        /// <summary>
        /// 执行多条SQL（Insert/Delete/Update）
        /// </summary>
        /// <param name="listCmdTextAndParams"></param>
        /// <returns></returns>
        public new int ExecSql(List<SqlCmdTextAndParams> listCmdTextAndParams)
        {
            return 0;
        }
        #endregion

        #region 查询
        /// <summary>
        /// 获得DbDataReader
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <returns></returns>
        public new DbDataReader ExecReader(string commandText, bool isProc)
        {
            return null;
        }
        /// <summary>
        /// 获得DbDataReader
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public new DbDataReader ExecReader(string commandText, bool isProc, DbParameter[] parameters)
        {
            return null;
        }
        /// <summary>
        /// 获得DbDataReader
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new DbDataReader ExecReader(string commandText, bool isProc, Hashtable parameters)
        {
            return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <returns></returns>
        public new object ExecScalar(string commandText, bool isProc)
        {
            object result = null;

            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <returns></returns>
        public new T ExecScalar<T>(string commandText, bool isProc)
        {
            object result = ExecScalar(commandText, isProc);
            return default(T);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new object ExecScalar(string commandText, bool isProc, DbParameter[] parameters)
        {
            object result = null;
            DataTable dt = new DataTable();
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new T ExecScalar<T>(string commandText, bool isProc, DbParameter[] parameters)
        {
            object result = ExecScalar(commandText, isProc, parameters);
            return default(T);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new object ExecScalar(string commandText, bool isProc, Hashtable parameters)
        {
            object result = null;
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new T ExecScalar<T>(string commandText, bool isProc, Hashtable parameters)
        {

            return default(T);
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <returns></returns>
        public new DataTable Query(string commandText, bool isProc)
        {
            DataTable dt = null;
            Open();

            return null;
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new DataTable Query(string commandText, bool isProc, DbParameter[] parameters)
        {

            return null;
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="srcDs">填充的数据集</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new DataTable Query(string commandText, bool isProc, Hashtable parameters)
        {

            return null;
        }
        /// <summary>
        /// 获得DataSet
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="srcDs">DataSet集</param>
        /// <returns></returns>
        public new DataSet Query(string commandText, bool isProc, string srcDs)
        {
            return null;
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="srcDs">填充的数据集</param>
        /// <param name="isProc">是否存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new DataSet Query(string commandText, bool isProc, DbParameter[] parameters, string srcDs)
        {
            return null;
        }
        /// <summary>
        /// 获得DataTable
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否是存储过程</param>
        /// <param name="srcDs">填充的数据集</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new DataSet Query(string commandText, bool isProc, Hashtable parameters, string srcDs)
        {
            return null;
        }
        #endregion

        #region 批量操作

        /// <summary>
        /// 大批量数据插入
        /// </summary>
        /// <param name="table">数据表</param>
        public new virtual void BulkInsert(DataTable table)
        {

        }

        /// <summary>
        /// 批量更新数据
        /// </summary>
        /// <param name="table">数据表</param>
        public new void BatchUpdate(DataTable table)
        {
            return;
        }

        /// <summary>
        /// 分批次批量删除数据
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="batchSize">每批次更新记录行数</param>
        /// <param name="interval">批次执行间隔(秒)</param>
        public new void BatchDelete(string commandText, int batchSize = 1000, int interval = 1)
        {
            return;
        }

        /// <summary>
        /// 分批次批量更新数据
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="batchSize">每批次更新记录行数</param>
        /// <param name="interval">批次执行间隔(秒)</param>
        public new void BatchUpdate(string commandText, int batchSize = 1000, int interval = 1)
        {
            return;
        }

        #endregion

        #region 其它
        /// <summary>
        /// 获取数据库日期函数
        /// </summary>
        /// <returns></returns>
        public new string GetDbTime()
        {
            return DateTime.Now.ToString();
        }
        /// <summary>
        ///  获得Sql字符串相加符号
        /// </summary>
        /// <param name="values">参数值</param>
        /// <returns>字符加</returns>
        public new string PlusSign(params string[] values)
        {
            string result = String.Empty;

            return result;
        }
        /// <summary>
        /// 创建提供程序对数据源类的实现的实例。
        /// </summary>
        /// <returns>数据源类的实现的实例</returns>
        //public new  abstract DbProviderFactory Instance();
        /// <summary>
        /// 检查参数的安全性
        /// </summary>
        /// <param name="value">参数</param>
        /// <returns>安全的参数</returns>
        public new string CheckSqlSafe(string value)
        {
            return value;
        }
        #endregion

        #region 私有方法

        #endregion

        #region IDisposable 成员

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (this._conn != null)
            {
                Close();
                this._conn = null;
            }
            if (this._com != null)
            {
                this._com = null;
            }
        }
        /// <summary>
        /// 打开数据库链接
        /// </summary>
        public void Open()
        {
            DirectoryInfo di = new DirectoryInfo(path);
            if (!di.Exists)
            {
                di.Create();
            }
            if (tableHT.Count == 0)
            {
                string[] arr = Directory.GetFileSystemEntries(path);
                foreach (string f in arr)
                {
                    string newStr = Path.GetFullPath(f);
                    string filename = Path.GetFileName(f);
                    string strExtension = Path.GetExtension(newStr).ToLower();
                    if (!strExtension.Contains(".ti"))// && !strExtension.Contains(".tb"))//.ti 表信息 .tb:表存储
                    {
                        continue;
                    }
                    tableHT.Add(filename, newStr);
                }
            }
            //加载数据
            //if (tableHT.Count > 0)
            //{
            //    string tPath = "";
            //    string tName = "";
            //    foreach (DictionaryEntry entry in tableHT)
            //    {
            //        tPath = entry.Value.ToString();
            //        tName = entry.Key.ToString();
            //    }
            //}
        }
        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        public void Close()
        {
            if (this._conn.State == ConnectionState.Open)
            {
                CommitTransaction();//提交事务
                this._conn.Close();
            }
            if (this._com.Parameters != null)
            {
                this._com.Parameters.Clear();
            }
        }
        #endregion

        #region 参数相关
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameters"></param>
        public new void AddParameters(DbParameter[] parameters)
        {
            return;
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameters">Hashtable</param>
        public new void AddParameters(Hashtable parameters)
        {
            return;
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameterName">参数名</param>
        /// <param name="value">参数值</param>
        public new void AddParameters(string parameterName, object value)
        {
            return;
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameterName">参数名</param>
        /// <param name="value">参数值</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">大小</param>
        /// <param name="direction"></param>
        public new virtual void AddParameters(string parameterName, object value, DbType dbType, Int32 size, ParameterDirection direction)
        {
            return;
        }
        /// <summary>
        /// 添加输出参数 主要是分页时输出总记录数
        /// </summary>
        public override void AddReturnPara()
        {

        }
        /// <summary>
        /// 获取不同数据库参数实例
        /// </summary>
        /// <returns></returns>
        public override DbParameter GetNewParameter()
        {
            return null;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="paraName"></param>
        /// <param name="paraType"></param>
        internal override void AddCustomePara(string paraName, ParaType paraType) { }
        #endregion

        #region 分页查询
        /// <summary>
        /// 带分页功能的选择[多条件查询,选择所有时只需把PageIndex/PageSize设置为0]
        /// </summary>
        /// <param name="primaryKey">主键或候选码</param>
        /// <param name="tableName">表名</param>
        /// <param name="showFields">显示字段</param>
        /// <param name="pageIndex">页索引</param>
        /// <param name="pageSize">每页数量[为0时默认选择所有]</param>
        /// <param name="where">查询条件</param>
        /// <param name="sortBy">排序字段</param>
        /// <param name="groupBy">分组字段</param>
        /// <param name="rowCount">返回行数</param>
        /// <returns></returns>
        public new virtual DataTable Query(string tableName, string primaryKey, string showFields, int pageIndex, int pageSize, string where, string sortBy, string groupBy, out int rowCount)
        {
            rowCount = 0;
            return null;
        }

        #endregion

        #region 存在性
        /// <summary>
        /// 记录是否存在
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <returns></returns>
        public new bool Exists(string commandText)
        {
            int result;
            return true;
        }
        /// <summary>
        /// 记录是否存在
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否是存储过程</param>
        /// <returns></returns>
        public new bool Exists(string commandText, bool isProc)
        {
            int result;
            return true;
        }
        /// <summary>
        /// 记录是否存在
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="isProc">是否是存储过程</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new bool Exists(string commandText, Hashtable parameters, bool isProc)
        {
            int result;
            return true;
        }
        /// <summary>
        /// 记录是否存在
        /// </summary>
        /// <param name="commandText">sql语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public new bool Exists(string commandText, Hashtable parameters)
        {
            int result;
            return true;
        }
        /// <summary>
        /// 列是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public new bool ColumnExists(string tableName, string columnName)
        {
            return true;
        }
        /// <summary>
        /// 表是否存在  2013-6-15添加，待验证
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public new bool TableExists(string tableName)
        {
            int count = 0;

            return count == 0 ? false : true;
        }
        #endregion

        #region 事务

        /// <summary>
        /// 开启事务
        /// </summary>
        public new void BeginTransaction()
        {
            return;
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public new void CommitTransaction()
        {
            return;
        }
        /// <summary>
        /// 事务回滚
        /// </summary>
        public new void RollbackTransaction()
        {
            return;
        }
        #endregion

        #region 数据库管理

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="backfile">备份文件</param>
        /// <returns>还原成功与否提示</returns>
        public new virtual bool RestoreDatabase(string dbName, string backfile)
        {
            return false;
        }

        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="dbName">数据库名</param>
        /// <param name="dbFileName">备份文件</param>
        /// <returns>是否备份成功</returns>
        public new virtual bool BackDataBase(string dbName, string dbFileName)
        {
            return false;
        }

        /// <summary>
        /// 新建数据库
        /// </summary>
        /// <param name="databaseName">数据库名</param>
        public new virtual bool CreateDataBase(string databaseName)
        {
            return false;
        }

        /// <summary>
        /// 删除数据库
        /// </summary>
        public new virtual bool DropDataBase(string databaseName)
        {
            return false;
        }
        #endregion

        #region 解析SQL

        /*处理原理
1：对SQL语句，按空格分格出数组。
2：对数组进行循环，遍历并标记出所有的关键字。
3：根据关键字，分拆：Select、Update、Delete、Insert、From、Where、Set、Into及表名，还有字段名和字段值及*，count(*),Distinct等关键字。
4：根据分析出来的条件，结合原有逻辑组合处理了。
         */
        /// <summary>
        /// 解析SQL语句 未完成
        /// </summary>
        /// <param name="sqlText">sql语句</param>
        private void FormatSqlText(string sqlText, ref DataTable dt)
        {
            if (dt == null)
            {
                return;
            }
            string[] items = sqlText.Split(' ');
            bool IsSelect = false;
            bool IsUpdate = false;
            bool IsDelete = false;
            bool IsFrom = false;
            bool IsGetCount = false;
            bool IsTopN = false;
            bool IsDistinct = false;

            int topN = -1;
            string whereSql = "";
            List<String> fieldList = new List<string>();//字段列表
            string tableName = "";
            foreach (string item in items)
            {
                switch (item.ToUpper())
                {
                    case "SELECT":
                        IsSelect = true;
                        break;
                    case "UPDATE":
                        IsUpdate = true;
                        break;
                    case "DELETE":
                        IsDelete = true;
                        break;
                    case "FROM":
                        IsFrom = true;
                        break;
                    case "COUNT(*)":
                        IsGetCount = true;
                        break;
                    case "WHERE":
                        whereSql = sqlText.Substring(sqlText.IndexOf(item) + item.Length + 1);
                        //该结束语句了。
                        return;
                    case "TOP":
                        if (IsSelect && !IsFrom)
                        {
                            IsTopN = true;
                        }
                        break;
                    case "DISTINCT":
                        if (IsSelect && !IsFrom)
                        {
                            IsDistinct = true;
                        }
                        break;
                    case "SET":
                        if (IsUpdate && !string.IsNullOrEmpty(tableName) && fieldList.Count == 0)
                        {
                            int start = sqlText.IndexOf(item) + item.Length;
                            int end = sqlText.ToLower().IndexOf("WHERE");
                            string itemText = sqlText.Substring(start, end == -1 ? sqlText.Length - start : end - start);
                            int quoteCount = 0, commaIndex = 0;

                            for (int i = 0; i < itemText.Length; i++)
                            {
                                if (i == itemText.Length - 1)
                                {
                                    string keyValue = itemText.Substring(commaIndex).Trim();
                                    if (!fieldList.Contains(keyValue))
                                    {
                                        fieldList.Add(keyValue);
                                    }
                                }
                                else
                                {
                                    switch (itemText[i])
                                    {
                                        case '\'':
                                            quoteCount++;
                                            break;
                                        case ',':
                                            if (quoteCount % 2 == 0)//双数，则允许分隔。
                                            {
                                                string keyValue = itemText.Substring(commaIndex, i - commaIndex).Trim();
                                                if (!fieldList.Contains(keyValue))
                                                {
                                                    fieldList.Add(keyValue);
                                                }
                                                commaIndex = i + 1;
                                            }
                                            break;

                                    }
                                }
                            }

                        }
                        break;
                    default:
                        if (IsTopN && topN == -1)
                        {
                            Int32.TryParse(item, out topN);//查询TopN
                        }
                        else if ((IsFrom || IsUpdate) && string.IsNullOrEmpty(tableName))
                        {
                            tableName = item;//获取表名。
                        }
                        else if (IsSelect && !IsFrom)//提取查询的中间条件。
                        {
                            #region Select语法解析
                            string temp = item.Trim(',');
                            switch (temp.ToLower())
                            {
                                case "*":
                                case "COUNT(*)":
                                case "TOP":
                                case "DISTION":
                                    break;
                                default:
                                    if (IsTopN && topN.ToString() == temp)
                                    {
                                        break;
                                    }
                                    if (!fieldList.Contains(temp))
                                    {
                                        fieldList.Add(temp.ToLower());
                                    }
                                    break;
                            }
                            #endregion
                        }
                        break;
                }
            }
        }
        #endregion
    }
}
